#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
#include <locale.h>

typedef int (*compfn)(const void*, const void*);

//#############################################################################
// Structures used
//#############################################################################
struct nTeachersYear{
	int year;
	int number;
};

struct nEstabYear{
	int year;
	int number;
};

struct nDegreeYear{
	int year;
	int number;
};

struct nTeachersEstabYear{

	char* estab;
	int number;
};

struct nTeachersDegreeYear{

	char* degree;
	int number;
};

struct nTeachersDegreeEstYear{

	char* degree;
	char* estab;
	int number;
};

struct lEstabYear{

	char* name;
};

struct lHolDeg{

	char* name;
	char* degree;
};
//#############################################################################



//#############################################################################
//qsort comparison functions
//#############################################################################

// qsort struct nTeachersYear by string comparison function
int strucCompare(struct nTeachersYear *a, struct nTeachersYear *b){ 
	if ( a->number < b->number){
		return -1;
	}
	else if (a->number > b->number){
		return 1;
	}
	else {
		return 0;
	}
} // end of strucCompare

// qsort struct nTeachersEstabYear by string comparison function
int strucnTeachersEstabYearCompare(struct nTeachersEstabYear *a, struct nTeachersEstabYear *b){ 
	if ( a->number < b->number){
		return -1;
	}
	else if (a->number > b->number){
		return 1;
	}
	else {
		return 0;
	}
} // end of strucnTeachersEstabYearCompare

// qsort struct nTeachersDegreeYear by string comparison function
int strucnTeachersDegreeYearCompare(struct nTeachersDegreeYear *a, struct nTeachersDegreeYear *b){ 
	if ( a->number < b->number){
		return -1;
	}
	else if (a->number > b->number){
		return 1;
	}
	else {
		return 0;
	}
} // end of strucnTeachersDegreeYearCompare


// qsort struct lHolDeg by string comparison function
int strucnHolDegYear(struct lHolDeg *a, struct lHolDeg *b){ 
	struct lHolDeg *ia = (struct lHolDeg *)a;
    struct lHolDeg *ib = (struct lHolDeg *)b;
    return strcmp(ia->name, ib->name);
} // end of strucnTeachersDegreeYearCompare

// qsort struct nTeachersDegreeEstYear by string comparison function
int strucnTeachersDegreEestYearCompare(struct nTeachersDegreeEstYear *a, struct nTeachersDegreeEstYear *b){ 
	if ( a->number < b->number){
		return -1;
	}
	else if (a->number > b->number){
		return 1;
	}
	else {
		return 0;
	}
} // end of strucnTeachersDegreeYearCompare


// qsort struct strucnLHolDeg by string comparison function
int strucnLHolDeg(struct lHolDeg *a, struct lHolDeg *b){ 
	struct lHolDeg *ia = (struct lHolDeg *)a;
    struct lHolDeg *ib = (struct lHolDeg *)b;
    return strcmp(ia->degree, ib->degree);
} // end of strucnTeachersDegreeYearCompare

// qsort struct lEstabYear by string comparison function
int strucnEstabYear(struct lEstabYear *a, struct lEstabYear *b) 
{ 
    struct lEstabYear *ia = (struct lEstabYear *)a;
    struct lEstabYear *ib = (struct lEstabYear *)b;
    return strcmp(ia->name, ib->name);
} // end of strucnStringCmp

// qsort int comparison function
int int_cmp(const void *a, const void *b) { 
    const int *ia = (const int *)a; // casting pointer types 
    const int *ib = (const int *)b;
    return *ia  - *ib; 
	// integer comparison: returns negative if b > a 
	// and positive if a > b 
} // end of int_cmp

//#############################################################################



//#############################################################################
//sqlite.exec callback functions
//#############################################################################

//callback to void int construction
static int callback(void **contagem, int argc, char **argv, char **azColName){
	*contagem = atoi( argv[0] );
	
	return 0;
} // end callback

//callback to the nTeachersEstabYear structure construction
static int callbackTuple(struct nTeachersEstabYear **nTeachersEstabYear, int argc, char **argv, char **azColName){

	(*nTeachersEstabYear)->estab = malloc((strlen(argv[0])+1)*sizeof(char));

	strcpy((*nTeachersEstabYear)->estab, argv[0]);
	(*nTeachersEstabYear)->number = atoi(argv[1]);
	(*nTeachersEstabYear)++;

    return 0;
} // end callbackTuple

//callback to the callbackHolDeg structure construction
static int callbackHolDeg(struct lHolDeg ** lHolDeg, int argc, char **argv, char **azColName){
	//alocation of memory
	(*lHolDeg)->name = malloc((strlen(argv[0])+1)*sizeof(char));
	(*lHolDeg)->degree = malloc((strlen(argv[1])+1)*sizeof(char));

	strcpy((*lHolDeg)->name, argv[0]);
	strcpy((*lHolDeg)->degree, argv[1]);
	(*lHolDeg)++;

    return 0;
} // end callbackHolDeg

//callback to the nTeachersDegreeEstYear structure construction
static int callbacknTeachersDegreeEstYear(struct nTeachersDegreeEstYear **nTeachersDegreeEstYear, int argc, char **argv, char **azColName){
	//alocation of memory
	(*nTeachersDegreeEstYear)->degree = malloc((strlen(argv[0])+1)*sizeof(char));
	(*nTeachersDegreeEstYear)->estab = malloc((strlen(argv[1])+1)*sizeof(char));

	strcpy((*nTeachersDegreeEstYear)->degree, argv[0]);
	strcpy((*nTeachersDegreeEstYear)->estab, argv[1]);
	(*nTeachersDegreeEstYear)->number = atoi(argv[2]);
	(*nTeachersDegreeEstYear)++;

    return 0;
} // end callbackTuple

//callback to the elEstabYear structure construction
static int callbackEstList(struct lEstabYear **lEstabYear, int argc, char **argv, char **azColName){
	//alocation of memory
	(*lEstabYear)->name = malloc((strlen(argv[0])+1)*sizeof(char));

	strcpy((*lEstabYear)->name, argv[0]);
	(*lEstabYear)++;

    return 0;
} // end callbackEstList

//#############################################################################



//#############################################################################
//qsort function made from a web example to bether understand the architecture
//#############################################################################
void mYqSort(void* base[], int nel, int width, compfn *function){
// from http://www.anyexample.com/programming/c/qsort__sorting_array_of_strings__integers_and_structs.xml
	
	qsort((void *) base,	// base — is a pointer to the beginning of data array
	nel,					// nel — is a number of elements
	width,					// width — is a size of each element (in bytes)
	(compfn)function);		// compar — is a callback function (pointer to function), which does comparison and returns positive or negative integer depending on //result.

} // end MyQsort



//#############################################################################
// function returning  the number of establishments per each year
// used to calculate the others statistics
//#############################################################################
struct nEstabYear* nEstabYear(){
	printf("nEstabYear\n\n");

	int i;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	//PyObject* theListEst = PyList_New(0);
	//struct nEstabYear* resultEst = malloc(10*sizeof(struct nEstabYear));
	struct nEstabYear* resultEst = malloc(10*sizeof(struct nEstabYear));
	int *contagem;
	
	for (i = 0; i <  10; i++){

		char string[25] = "rebidesDB/rebides200X";
        string[20] = i + '0';
		//puts(string);
        strcat (string, ".db");
        char* dbName = (string);
        char* sqlInst = "SELECT COUNT(DISTINCT estabelecimento) FROM fichas_docencia ORDER BY estabelecimento";

        rc = sqlite3_open(dbName, &db);

        rc = sqlite3_exec(db, sqlInst, callback, &contagem, &zErrMsg);
        if( rc!=SQLITE_OK ){
                fprintf(stderr, "SQL error: %s\n", zErrMsg);
                sqlite3_free(zErrMsg);
        }
		sqlite3_close(db);
		//struct nEstabYear resultEstAlias;
		resultEst[i].year = i;
		resultEst[i].number = contagem;	
	}

	/*
	for(i = 0; i < 10; i++){
		PyObject* tEstYear = PyTuple_New(2);
		PyTuple_SetItem( tEstYear, 0, PyInt_FromLong(resultEst[i].year));
		PyTuple_SetItem( tEstYear, 1, PyInt_FromLong(resultEst[i].number));
		PyList_Append(theListEst, tEstYear);
	}
	*/
	return resultEst;		
	
} // end nEstabYear



//#############################################################################
// function returning  the number of degrees per each year
// used to calculate the others statistics
//#############################################################################
struct nDegreeYear* nDegreeYear(){
	printf("nDegreeYear\n\n");

	int i;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	//PyObject* theListDeg = PyList_New(0);
	//struct nDegreeYear* resultDeg = malloc(10*sizeof(struct nDegreeYear));
	struct nDegreeYear* resultDeg = malloc(10*sizeof(struct nDegreeYear));
	int *contagem;
	
	for (i = 0; i <  10; i++){

		char string[25] = "rebidesDB/rebides200X";
        string[20] = i + '0';
		puts(string);
        strcat (string, ".db");
        char* dbName = (string);
        char* sqlInst = "SELECT COUNT(DISTINCT grau) FROM fichas_docencia ORDER BY grau";

        rc = sqlite3_open(dbName, &db);

        rc = sqlite3_exec(db, sqlInst, callback, &contagem, &zErrMsg);
        if( rc!=SQLITE_OK ){
                fprintf(stderr, "SQL error: %s\n", zErrMsg);
                sqlite3_free(zErrMsg);
        }
		sqlite3_close(db);
		
		resultDeg[i].year = i;
		resultDeg[i].number = contagem;	
	}
	/*
	 for(i = 0; i < 10; i++){
		 PyObject* tDegreeYear = PyTuple_New(2);
		 PyTuple_SetItem( tDegreeYear, 0, PyInt_FromLong(resultDeg[i].year));
		 PyTuple_SetItem( tDegreeYear, 1, PyInt_FromLong(resultDeg[i].number));
		 PyList_Append(theListDeg, tDegreeYear);
	}
	*/
	
	return resultDeg;		
	
} // end nDegreeYear



//#############################################################################
// function returning  the number of establishments and degree
// used to calculate the others statistics
//#############################################################################
int* nEstabDeg(){
	printf("nEstabDeg\n\n");

	int i;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	int contagem;

	int* theDegEstList = malloc(10*sizeof(int));
	
	for (i = 0; i <  10; i++){

		char string[25] = "rebidesDB/rebides200X";
        string[20] = i + '0';
		puts(string);
        strcat (string, ".db");
        char* dbName = (string);
        char* sqlInst = "SELECT sum(estabelecimentos) FROM (SELECT count(Distinct estabelecimento)\
						 AS estabelecimentos FROM fichas_docencia GROUP by grau ORDER by estabelecimento)";

        rc = sqlite3_open(dbName, &db);

        rc = sqlite3_exec(db, sqlInst, callback, &contagem, &zErrMsg);
        if( rc!=SQLITE_OK ){
                fprintf(stderr, "SQL error: %s\n", zErrMsg);
                sqlite3_free(zErrMsg);
        }
		sqlite3_close(db);
		
		theDegEstList[i] = contagem;
		printf("theDegEstList[%d] = %d\n", i, theDegEstList[i]);
	}
	
	return theDegEstList;		
	
} // end nDegreeYear


//#############################################################################
// function returning  the number of teachers per year
// used to calculate the others statistics
//#############################################################################
struct nTeachersYear* nTeachYear(){
	printf("nTeachYear()\n\n");

	int i;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	//PyObject* theList = PyList_New(0);
	//struct nTeachersYear* result = malloc(10*sizeof(struct nTeachersYear));
	struct nTeachersYear* result = malloc(10*sizeof(struct nTeachersYear));
	
	int *contagem;
	
	for (i = 0; i <  10; i++){

		char string[25] = "rebidesDB/rebides200X";
        string[20] = i + '0';
		puts(string);
        strcat (string, ".db");
        char* dbName = (string);
        char* sqlInst = "SELECT COUNT(*) FROM docentes";

        rc = sqlite3_open(dbName, &db);

        rc = sqlite3_exec(db, sqlInst, callback, &contagem, &zErrMsg);
        if( rc!=SQLITE_OK ){
                fprintf(stderr, "SQL error: %s\n", zErrMsg);
                sqlite3_free(zErrMsg);
        }

		sqlite3_close(db);

		result[i].year = i;
		result[i].number = contagem;
		
		
	}

	mYqSort(result, 10, sizeof(struct nTeachersYear), strucCompare);

	/*
	for(i = 0; i < 10; i++){
		PyObject* tYear = PyTuple_New(2);
		PyTuple_SetItem( tYear, 0, PyInt_FromLong(result[i].year));
		PyTuple_SetItem( tYear, 1, PyInt_FromLong(result[i].number));
		PyList_Append(theList, tYear);
	}
	*/

	return result;

	
} // end teachers_Year



//#############################################################################
// function returning  the number of teachers and degree
// used to calculate the others statistics
//#############################################################################
int* nTeachDeg(){
	printf("nEstabDeg\n\n");

	int i;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	int contagem;

	int* theTeachEstList = malloc(10*sizeof(int));
	
	for (i = 0; i <  10; i++){

		char string[25] = "rebidesDB/rebides200X";
        string[20] = i + '0';
		puts(string);
        strcat (string, ".db");
        char* dbName = (string);
        char* sqlInst = "Select COUNT (*) as total FROM (SELECT docente, grau  FROM fichas_docencia\
							GROUP BY docente, grau\
							ORDER BY docente, grau)";

        rc = sqlite3_open(dbName, &db);

        rc = sqlite3_exec(db, sqlInst, callback, &contagem, &zErrMsg);
        if( rc!=SQLITE_OK ){
                fprintf(stderr, "SQL error: %s\n", zErrMsg);
                sqlite3_free(zErrMsg);
        }
		sqlite3_close(db);
		
		theTeachEstList[i] = contagem;
		printf("theTeachEstList[%d] = %d\n", i, theTeachEstList[i]);
	}
	
	return theTeachEstList;		
	
} // end nTeachDeg



//#############################################################################
// function returning the number of teachers per establishment
// used to calculate the others statistics
//#############################################################################
struct nTeachersEstabYear* nTeachEstYear(int year, int nEstab){
	printf("nTeachEstYear\n");
	int i = 0;
	int j = 0;
	int rc = 0;	
	int nEst = 0;
	int sizeoftrings = 0;
	sqlite3 *db;

	char *zErrMsg = 0;
	char* sqlInst;

	//PyObject* theListTeachEst = PyList_New(0);
	//PyObject* ListnEst = PyList_New(10);

	struct nTeachersEstabYear* resultTEY = malloc(10*sizeof(struct nTeachersEstabYear));
	struct nTeachersEstabYear* resultTEYAlias = malloc(10*sizeof(struct nTeachersEstabYear));
	struct nEstabYear* ListnEst = malloc(10*sizeof(struct nEstabYear));


	ListnEst = nEstabYear();

	/*// to calculate the total nº of institutions to allocate memory
	nEst = PyInt_AS_LONG(PyTuple_GetItem(PyList_GET_ITEM(ListnEst, 
	                                                     PyInt_AsSsize_t(PyInt_FromLong(year))),PyInt_AsSsize_t(PyInt_FromLong(1))));*/
	/*//malloc with the entire list
	resultTEY = malloc(nEst*sizeof(struct nTeachersEstabYear));*/
	resultTEY = malloc(nEstab*sizeof(struct nTeachersEstabYear)); // allocates only for nEstab institutions

	resultTEYAlias = resultTEY;

	char string[25] = "rebidesDB/rebides200X";
	string[20] = i + '0';
	//puts(string);
	strcat (string, ".db");
	char* dbName = (string);

	/*// to return the entire list
	char* sqlInst = "SELECT DISTINCT estabelecimento, COUNT(DISTINCT docente)\
						AS number FROM fichas_docencia GROUP BY estabelecimento ORDER by number";*/
	// returns only the nEstab first


	
	char first[] = "SELECT DISTINCT estabelecimento, COUNT(DISTINCT docente)\
						AS number FROM fichas_docencia GROUP BY estabelecimento ORDER by number DESC LIMIT ";
	char* second = malloc(3*sizeof(char));
	sprintf(second, "%d", nEstab);
	
	sqlInst = malloc((strlen(first)+strlen(second)+1)*sizeof(char));
	strcpy (sqlInst, first);
	strcat(sqlInst, second);
		

	rc = sqlite3_open(dbName, &db);

	rc = sqlite3_exec(db, sqlInst, callbackTuple, &resultTEY, &zErrMsg);
	if( rc!=SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}
	
	free(ListnEst);

	return resultTEYAlias;		

} // end nTeachEstYear


//#############################################################################
// statistic with number of teachers per each year
//#############################################################################
struct nTeachersYear* teachers_Year(){
	printf("Processing statistics with number of teachers\n\n");

	int i;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	//PyObject* theList = PyList_New(0);
	//struct nTeachersYear* result[10];
	struct nTeachersYear* result = malloc(10*sizeof(struct nTeachersYear));

	printf("OK");
	
	int *contagem;
	/*
	FILE* file;
	file = fopen( "Rebides.txt", "w" );
	*/
	
	for (i = 0; i <  10; i++){
		
		char string[25] = "rebidesDB/rebides200X";
        string[20] = i + '0';
		puts(string);
        strcat (string, ".db");
        char* dbName = (string);
        char* sqlInst = "SELECT COUNT(*) FROM docentes";

        rc = sqlite3_open(dbName, &db);

        rc = sqlite3_exec(db, sqlInst, callback, &contagem, &zErrMsg);
        if( rc!=SQLITE_OK ){
                fprintf(stderr, "SQL error: %s\n", zErrMsg);
                sqlite3_free(zErrMsg);
        }

		sqlite3_close(db);
		result[i].year = i;
		result[i].number = contagem;
		printf("Year %d = %d\n", result[i].year, result[i].number);
	}

	mYqSort(result, 10, sizeof(struct nTeachersYear), strucCompare);

    /*
	fprintf(file, "############################################################\n");
	fprintf(file, "#           Lista do nº de Professores Por Ano             #\n");
	fprintf(file, "#----------------------------------------------------------#\n");
	fprintf(file, "#    Organizado por ordem crescente do nº de professores   #\n");
	fprintf(file, "############################################################\n\n");
	

	for(i = 0; i < 10; i++){
		PyObject* tYear = PyTuple_New(2);
		PyTuple_SetItem( tYear, 0, PyInt_FromLong(result[i].year));
		PyTuple_SetItem( tYear, 1, PyInt_FromLong(result[i].number));
		PyList_Append(theList, tYear);

		fprintf(file, "+ O ano 200%d teve um total de : %d docentes\n", result[i].year, result[i].number);
		printf("+ O ano 200%d teve um total de : %d docentes\n", result[i].year, result[i].number);
	}

	fprintf(file, "\n############################################################\n\n");

	fclose(file);*/

	return result;


} // end teachers_Year



//#############################################################################
// statistic with number of teachers per establishment each year
//#############################################################################
struct nTeachersEstabYear* teachEstYear(){
	printf("Processing statistics with number of teachers per establishment\n\n");
	setlocale( LC_ALL, "Portuguese" );

	int i = 0;
	int j = 0;
	int rc = 0;	
	int nEst = 0;
	sqlite3 *db;

	char *zErrMsg = 0;

	//PyObject* theListTeachEst = PyList_New(0);
	//PyObject* ListnEst = PyList_New(10);
	//struct nTeachersYear* ListnEst[10];

	struct nEstabYear* ListnEst = malloc(10*sizeof(struct nEstabYear));
	
	struct nTeachersEstabYear* resultTEY;
	struct nTeachersEstabYear* resultTEYAlias;
	

	//FILE *file;
	//file = fopen( "Rebides.txt", "a" );

	ListnEst = nEstabYear();

	/*
	fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n");
	fprintf(file, "&  Estatistica do nº de Profs por Estabelecimentos por Ano &\n");
	fprintf(file, "&----------------------------------------------------------&\n");
	fprintf(file, "& Organizado por ordem crescente do nº de professores/ano  &\n");
	fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
	*/

	for (i = 0; i <  10; i++){

		nEst = ListnEst[i].number;

		resultTEY = malloc(nEst*sizeof(struct nTeachersEstabYear));
		
		resultTEYAlias = resultTEY;
		
		char string[25] = "rebidesDB/rebides200X";
        string[20] = i + '0';
		puts(string);
		strcat (string, ".db");
		char* dbName = (string);

		char* sqlInst = "SELECT DISTINCT estabelecimento, COUNT(DISTINCT docente) AS number FROM fichas_docencia GROUP BY estabelecimento ORDER by number";

		rc = sqlite3_open(dbName, &db);

		rc = sqlite3_exec(db, sqlInst, callbackTuple, &resultTEY, &zErrMsg);
		if( rc!=SQLITE_OK ){
			fprintf(stderr, "SQL error: %s\n", zErrMsg);
			sqlite3_free(zErrMsg);
		}
		
		mYqSort(resultTEYAlias, nEst, sizeof(struct nTeachersEstabYear), strucnTeachersEstabYearCompare);

		/*
		fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n");
		fprintf(file, "&                     Ano de 200%d:                        &\n", i);
		fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
		printf("Year of 200%d:\n", i);

		
		for(j = 0; j < nEst; j++){
			
			PyObject* tTeachEstYear = PyTuple_New(3);
			PyTuple_SetItem(tTeachEstYear, PyInt_AsSsize_t(PyInt_FromLong(0)), PyInt_FromLong(i));
			PyTuple_SetItem(tTeachEstYear, PyInt_AsSsize_t(PyInt_FromLong(1)), PyString_FromFormat("%s",resultTEYAlias[j].estab));
			PyTuple_SetItem(tTeachEstYear, PyInt_AsSsize_t(PyInt_FromLong(2)), PyInt_FromLong(resultTEYAlias[j].number));
			PyList_Append(theListTeachEst, tTeachEstYear);

			fprintf(file, "%dº - %s: teve um total de : %d professores\n", (j + 1), resultTEYAlias[j].estab, resultTEYAlias[j].number);
			//printf("%dº - %s: teve um total de : %d professores\n", (j + 1), resultTEYAlias[j].estab, resultTEYAlias[j].number);
			free(resultTEYAlias[j].estab);
		}
		*/

		
		//printf("Year of 200%d OK!\n", i);				
		//fprintf(file, "\n");
	}
	free(ListnEst);

	//fprintf(file, "\n&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
	//fclose(file);

	return resultTEYAlias;		

} // end teachEstYear



//#############################################################################
// statistic with number of teachers per degree each year
//#############################################################################
struct nDegreeYear* teachDegYear(){
	printf("Processing statistics with number of teachers per degree\n\n");
	setlocale( LC_ALL, "Portuguese" );

	int i = 0;
	int j = 0;
	int rc = 0;	
	int nDeg = 0;
	sqlite3 *db;

	char *zErrMsg = 0;

	//PyObject* theListTeachDeg = PyList_New(0);
	//PyObject* ListnDeg = PyList_New(10);
	struct nDegreeYear* ListnDeg = malloc(10*sizeof(struct nDegreeYear));
	

	struct nTeachersDegreeYear* resultTDY;
	struct nTeachersDegreeYear* resultTDYAlias;

	ListnDeg = nDegreeYear();

	/*
	FILE *file;
	file = fopen( "Rebides.txt", "a" );
	fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n");
	fprintf(file, "&        Estatistica do nº de Profs por Grau por Ano       &\n");
	fprintf(file, "&----------------------------------------------------------&\n");
	fprintf(file, "& Organizado por ordem crescente do nº de professores/ano  &\n");
	fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
	*/

	for (i = 0; i <  10; i++){

		nDeg = ListnDeg[i].number;

		resultTDY = malloc(nDeg*sizeof(struct nTeachersDegreeYear));

		resultTDYAlias = resultTDY;
		
		char string[25] = "rebidesDB/rebides200X";
        string[20] = i + '0';
		puts(string);
		strcat (string, ".db");
		char* dbName = (string);

		char* sqlInst = "SELECT DISTINCT grau, COUNT(DISTINCT docente) AS number FROM fichas_docencia GROUP BY grau ORDER by number";

		rc = sqlite3_open(dbName, &db);

		rc = sqlite3_exec(db, sqlInst, callbackTuple, &resultTDY, &zErrMsg);
		if( rc!=SQLITE_OK ){
			fprintf(stderr, "SQL error: %s\n", zErrMsg);
			sqlite3_free(zErrMsg);
		}

		mYqSort(resultTDYAlias, nDeg, sizeof(struct nTeachersDegreeYear), strucnTeachersDegreeYearCompare);

		/*
		fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n");
		fprintf(file, "&                     Ano de 200%d:                        &\n", i);
		fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
		printf("Year of 200%d:\n", i);

		
		for(j = 0; j < nDeg; j++){

			PyObject* tTeachDegreeYear = PyTuple_New(3);
			PyTuple_SetItem(tTeachDegreeYear, PyInt_AsSsize_t(PyInt_FromLong(0)), PyInt_FromLong(i));
			PyTuple_SetItem(tTeachDegreeYear, PyInt_AsSsize_t(PyInt_FromLong(1)), PyString_FromFormat("%s",resultTDYAlias[j].degree));
			PyTuple_SetItem(tTeachDegreeYear, PyInt_AsSsize_t(PyInt_FromLong(2)), PyInt_FromLong(resultTDYAlias[j].number));
			PyList_Append(theListTeachDeg, tTeachDegreeYear);
			fprintf(file, "%dº - %s: teve um total de : %d professores\n", (j + 1), resultTDYAlias[j].degree, resultTDYAlias[j].number);
			//printf("%dº - %s: teve um total de : %d professores\n", (j + 1), resultTDYAlias[j].degree, resultTDYAlias[j].number);
			free(resultTDYAlias[j].degree);
		}
		free(resultTDYAlias)
		
		fprintf(file, "\n");
		printf("Year of 200%d OK!\n", i);
		printf("\n");*/
	}

	//fprintf(file, "\n&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
	//fclose(file);
	free(ListnDeg);

	return resultTDYAlias;		

} // end teachDegYear	



//#############################################################################
// statistic with number of teachers per degree and per establishment each year
//#############################################################################
struct nTeachersDegreeEstYear* teachDegEstYear(){
	printf("Processing statistics with number of teachers per degree and per establishment\n\n");
	setlocale( LC_ALL, "Portuguese" );

	int i = 0;
	int j = 0;
	int rc = 0;	
	sqlite3 *db;

	char *zErrMsg = 0;

	//PyObject* theListTeachDeg = PyList_New(0);

	int* degreeList = nEstabDeg();

	struct nTeachersDegreeEstYear* resultTDEY;
	struct nTeachersDegreeEstYear* resultTDEYAlias;

	/*
	FILE *file;
	file = fopen( "Rebides.txt", "a" );

	fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n");
	fprintf(file, "& Estatistica do nº de Profs por Grau/Estabelecimento/Ano  &\n");
	fprintf(file, "&----------------------------------------------------------&\n");
	fprintf(file, "& Organizado por ordem crescente do nº de professores/ano  &\n");
	fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
	*/

	for (i = 0; i <  10; i++){

		//printf("\nList: %d \n", (degreeList[i]));

		resultTDEY = malloc((degreeList[i])*sizeof(struct nTeachersDegreeEstYear));

		resultTDEYAlias = resultTDEY;
		
		char string[25] = "rebidesDB/rebides200X";
        string[20] = i + '0';
		puts(string);
		strcat (string, ".db");
		char* dbName = (string);

		char* sqlInst = "SELECT DISTINCT grau, estabelecimento, COUNT(docente) AS total FROM fichas_docencia GROUP BY grau, estabelecimento";

		rc = sqlite3_open(dbName, &db);

		rc = sqlite3_exec(db, sqlInst, callbacknTeachersDegreeEstYear, &resultTDEY, &zErrMsg);
		if( rc!=SQLITE_OK ){
			fprintf(stderr, "SQL error: %s\n", zErrMsg);
			sqlite3_free(zErrMsg);
		}

		/*
		fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n");
		fprintf(file, "&                     Ano de 200%d:                        &\n", i);
		fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
		printf("Year of 200%d:\n", i);

		for(j = 0; j < degreeList[i]; j++){

			PyObject* tTeachDegreeEstYear = PyTuple_New(4);
			PyTuple_SetItem(tTeachDegreeEstYear, PyInt_AsSsize_t(PyInt_FromLong(0)), PyInt_FromLong(i));
			PyTuple_SetItem(tTeachDegreeEstYear, PyInt_AsSsize_t(PyInt_FromLong(1)), PyString_FromFormat("%s",resultTDEYAlias[j].degree));
			PyTuple_SetItem(tTeachDegreeEstYear, PyInt_AsSsize_t(PyInt_FromLong(2)), PyString_FromFormat("%s",resultTDEYAlias[j].estab));
			PyTuple_SetItem(tTeachDegreeEstYear, PyInt_AsSsize_t(PyInt_FromLong(3)), PyInt_FromLong(resultTDEYAlias[j].number));
			PyList_Append(theListTeachDeg, tTeachDegreeEstYear);

			fprintf(file, "%dº - %s - %s: %d professores\n", (j + 1), resultTDEYAlias[j].degree, resultTDEYAlias[j].estab, resultTDEYAlias[j].number);
			//printf("%dº - %s - %s: %d professores\n", (k + 1), resultTDEYAlias[j].degree, resultTDEYAlias[k].estab, resultTDEYAlias[k].number);

			free(resultTDEYAlias[j].degree);
			free(resultTDEYAlias[j].estab);
		}

		free(resultTDEYAlias);
		printf("Year of 200%d OK!\n", i);
		fprintf(file, "\n");
		*/
	}

	//fprintf(file, "\n&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
	//fclose(file);
	free(degreeList);

	return resultTDEYAlias;		

} // end teachDegEstYear


//#############################################################################
// List the establishments on each year
//#############################################################################
struct lEstabYear* listEstabYear(){
	printf("Processing lists of establishments\n\n");
	setlocale( LC_ALL, "Portuguese" );

	int i = 0;
	int j = 0;
	int rc = 0;	
	int nEst = 0;
	sqlite3 *db;

	char *zErrMsg = 0;

	//PyObject* theListEstYear = PyList_New(0);
	//PyObject* listnEY = PyList_New(10);

	struct lEstabYear* resultEY;
	struct lEstabYear* resultEYAlias;
	struct nEstabYear* listnEY = malloc(10*sizeof(struct nEstabYear));

	listnEY = nEstabYear();
	
	/*
	FILE *file;
	file = fopen( "Rebides.txt", "a" );

	

	fprintf(file, "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n");
	fprintf(file, ":           Lista dos Estabelecimentos por Ano             :\n");
	fprintf(file, ":----------------------------------------------------------:\n");
	fprintf(file, ":           Organizado por ordem alfabética/ano            :\n");
	fprintf(file, "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n\n");
	*/

	for (i = 0; i <  10; i++){

		nEst = listnEY[i].number;

		resultEY = malloc(nEst*sizeof(struct lEstabYear));

		resultEYAlias = resultEY;
		
		char string[25] = "rebidesDB/rebides200X";
        string[20] = i + '0';
		puts(string);
		strcat (string, ".db");
		char* dbName = (string);

		char* sqlInst = "SELECT DISTINCT estabelecimento FROM fichas_docencia";

		rc = sqlite3_open(dbName, &db);

		rc = sqlite3_exec(db, sqlInst, callbackEstList, &resultEY, &zErrMsg);
		if( rc!=SQLITE_OK ){
			fprintf(stderr, "SQL error: %s\n", zErrMsg);
			sqlite3_free(zErrMsg);
		}
		
		mYqSort(resultEYAlias, nEst, sizeof(struct lEstabYear), strucnEstabYear);

		/*
		fprintf(file, "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n");
		fprintf(file, ":                     Ano de 200%d                         :\n", i);
		fprintf(file, "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n\n");
		printf("Year of 200%d\n", i);

		for(j = 0; j < nEst; j++){

			PyObject* tEstYear = PyTuple_New(2);
			PyTuple_SetItem(tEstYear, PyInt_AsSsize_t(PyInt_FromLong(0)), PyInt_FromLong(j));
			PyTuple_SetItem(tEstYear, PyInt_AsSsize_t(PyInt_FromLong(1)), PyString_FromFormat("%s",resultEYAlias[j].name));
			PyList_Append(theListEstYear, tEstYear);

			fprintf(file, "%dº - %s\n", (j + 1), resultEYAlias[j].name);
			//printf("%dº - %s\n", (j + 1), resultEYAlias[j].name);
			free(resultEYAlias[j].name);
		}

		free(resultEYAlias);
		printf("Year of 200%d OK!\n", i);
		
		fprintf(file, "\n");
		printf("\n");
		*/
	}

	//fprintf(file, "\n::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n\n");
	//fclose(file);
	free(listnEY);
	return resultEY;		

} // listEstabYear


//#############################################################################
// List of holders of a degree
//#############################################################################
struct lHolDeg* listHolDegYear(){
	printf("Processing lists of holders of a degree\n\n");
	setlocale( LC_ALL, "Portuguese" );

	int i = 0;
	int j = 0;
	int rc = 0;	
	int nDeg = 0;
	sqlite3 *db;

	char *zErrMsg = 0;

	//PyObject* theListTeachDegYear = PyList_New(0);
	int* ListTeachDeg;

	struct lHolDeg* resultLTEY;
	struct lHolDeg* resultLTEYAlias;
	

	FILE *file;
	file = fopen( "Rebides.txt", "a" );

	ListTeachDeg = nTeachDeg();

	/*
	fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n");
	fprintf(file, "&              Lista de Profs por Grau por Ano             &\n");
	fprintf(file, "&----------------------------------------------------------&\n");
	fprintf(file, "&    Organizado por ordem alfabetica de professores/ano    &\n");
	fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
	*/

	for (i = 0; i <  10; i++){

		nDeg = ListTeachDeg[i];

		resultLTEY = malloc(nDeg*sizeof(struct lHolDeg));

		resultLTEYAlias = resultLTEY;
		
		char string[25] = "rebidesDB/rebides200X";
        string[20] = i + '0';
		//puts(string);
		strcat (string, ".db");
		char* dbName = (string);

		char* sqlInst = "SELECT docente, grau FROM fichas_docencia\
							GROUP BY docente, grau\
							ORDER BY docente, grau";
		
		rc = sqlite3_open(dbName, &db);

		rc = sqlite3_exec(db, sqlInst, callbackHolDeg, &resultLTEY, &zErrMsg);
		if( rc!=SQLITE_OK ){
			fprintf(stderr, "SQL error: %s\n", zErrMsg);
			sqlite3_free(zErrMsg);
		}
		
		mYqSort(resultLTEYAlias, nDeg, sizeof(struct lHolDeg), strucnLHolDeg);

		/*
		fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n");
		fprintf(file, "$                     Ano de 200%d:                        &\n", i);
		fprintf(file, "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
		printf("Ano de 200%d:\n", i);
		

		for(j = 0; j < nDeg; j++){
			
			PyObject* tLTeachDegreeYear = PyTuple_New(3);
			PyTuple_SetItem(tLTeachDegreeYear, PyInt_AsSsize_t(PyInt_FromLong(0)), PyInt_FromLong(i));
			PyTuple_SetItem(tLTeachDegreeYear, PyInt_AsSsize_t(PyInt_FromLong(1)), PyString_FromFormat("%s",resultLTEYAlias[j].name));
			PyTuple_SetItem(tLTeachDegreeYear, PyInt_AsSsize_t(PyInt_FromLong(2)), PyString_FromFormat("%s",resultLTEYAlias[j].degree));
			PyList_Append(theListTeachDegYear, tLTeachDegreeYear);

			fprintf(file, "%dº - %s - %s professores\n", (j + 1), resultLTEYAlias[j].name, resultLTEYAlias[j].degree);
			//printf("%dº - %s - %s professores\n", (j + 1), resultLTEYAlias[j].name, resultLTEYAlias[j].degree);
			free(resultLTEYAlias[j].name);
			free(resultLTEYAlias[j].degree);
		}

		free(resultLTEYAlias);
		
		fprintf(file, "\n");
		printf("Ano de 200%d: OK\n", i);
		printf("\n");
		*/
	}

	//fprintf(file, "\n&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n\n");
	//fclose(file);

	return resultLTEY;		

} // end of listHolDegYear

//#############################################################################
// List of leaving/new personnel per establishment each year
//#############################################################################
struct nTeachersEstabYear* teachLeavingEstYear(){
	printf("Processing list of leaving/new personnel per establishment each year\n\n");
	
	int i = 0;
	int j = 0;
	int k = 0;
	int nEstJ = 0;
	int nEstK = 0;
	int nEstMAX = 10;

	//PyObject* theListTeachLeavingEst = PyList_New(0);
	//PyObject* ListnEstab = PyList_New(10);
	struct nEstabYear* ListnEstab = malloc(10*sizeof(struct nEstabYear));

	struct nTeachersEstabYear* resultNTEY;
	struct nTeachersEstabYear* resultNTEYAlias;
	struct nTeachersEstabYear* listYear;
	struct nTeachersEstabYear* listYearA;
	struct nTeachersEstabYear* listYearAfter;
	struct nTeachersEstabYear* listYearAfterA;
	
	/*
	FILE *file;
	file = fopen( "Rebides.txt", "a" );

	fprintf(file, "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n");
	fprintf(file, "<     Lista do nº de entradas/saidas por instituição/ano   >\n");
	fprintf(file, "<---------------------------------------------------------->\n");
	fprintf(file, "< Organizado por ordem crescente do nº de professores/ano  >\n");
	fprintf(file, "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n\n");
	*/

	ListnEstab = nEstabYear();	

	for (i = 0; i <  9; i++){

		/*
		fprintf(file, "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n");
		fprintf(file, "<                     Ano de 200%d:                         >\n", i+1);
		fprintf(file, "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n\n");
		

		printf("Year of 200%d:\n", i+1);
		*/
		
		nEstJ = ListnEstab[i+1].number;

		nEstMAX = nEstJ; //change the nEstMAX to the desired nº os institutions to compare

		listYearAfter = malloc(nEstMAX*sizeof(struct nTeachersEstabYear));//change nEstMAX to nEstJ to use entire list
		listYearAfter = nTeachEstYear(i+1, nEstMAX); //change nEstMAX to nEstJ to use entire list
		listYearAfterA = listYearAfter;

		nEstK = ListnEstab[i].number;

		listYear = malloc(nEstK*sizeof(struct nTeachersEstabYear)); // allocates nEstK memory
		listYear = nTeachEstYear(i, nEstK);
		listYearA = listYear;

		resultNTEY = malloc(nEstMAX*sizeof(struct nTeachersEstabYear)); // allocate memory only for maxSize institutions	
		resultNTEYAlias = resultNTEY;
		
		for (j = 0; j < nEstMAX; j++){	// to use with the entire list
			
			
			resultNTEYAlias[j].estab = listYearAfterA[j].estab;
			resultNTEYAlias[j].number = 0;
			
			int eXistenZ = 0;

			for (k = 0; k < nEstK; k++){
				
				if (strcmp(listYearAfterA[j].estab, listYearA[k].estab)== 0){

					printf("%d- %s: %d of %d\n",j+1, listYearAfterA[j].estab, j+1, nEstJ);
					//fprintf(file, "%d- %s: %d of %d\n",j+1, listYearAfterA[j].estab, j+1, nEstJ);
					eXistenZ += 1;
					
					resultNTEYAlias[j].number = (listYearAfterA[j].number - listYearA[k].number);

					if (resultNTEYAlias[j].number < 0){
						printf("   There were %d teachers leaving the institution\n", (-1*resultNTEYAlias[j].number));
						//fprintf(file, "   There were %d teachers leaving the institution\n", resultNTEYAlias[j].number);
					} else if (resultNTEYAlias[j].number > 0){
						printf("   There were %d new teachers in the institution\n", resultNTEYAlias[j].number);
						//fprintf(file, "   There were %d new teachers in the institution\n", resultNTEYAlias[j].number);
					} else if (resultNTEYAlias[j].number == 0){
						printf("   There were no changes in this institution\n");
						//fprintf(file, "  There were no changes in this institution\n");
					}
				}

			}
			
			if (eXistenZ == 0){

				printf("%d- %s: %d of %d\n",j+1, listYearAfterA[j].estab, j+1, nEstJ);
				//fprintf(file, "%d- %s: %d of %d\n",j+1, listYearAfterA[j].estab, j+1, nEstJ);
				resultNTEYAlias[j].number = listYearAfterA[j].number;
				printf("   In 200%d this institution didn't exist- there were created %d new openings.\n", i, resultNTEYAlias[j].number);
				//fprintf(file, "   In 200%d this institution didn't exist- there were created %d new openings.\n", i, resultNTEYAlias[j].number);

			}
			
			
		}

		free(listYear);
		free(listYearAfter);

		mYqSort(resultNTEYAlias, nEstMAX, sizeof(struct nTeachersEstabYear), strucnTeachersEstabYearCompare); //change nEstMAX to nEstJ to use entire list	
		/*
		for(j = 0; j < nEstMAX; j++){	// //change nEstMAX to nEstJ to use entire list			

			PyObject* tListTeachLeavingEst = PyTuple_New(3);
			PyTuple_SetItem(tListTeachLeavingEst, PyInt_AsSsize_t(PyInt_FromLong(0)), PyInt_FromLong(i));
			PyTuple_SetItem(tListTeachLeavingEst, PyInt_AsSsize_t(PyInt_FromLong(1)), PyString_FromFormat("%s",resultNTEYAlias[j].estab));
			PyTuple_SetItem(tListTeachLeavingEst, PyInt_AsSsize_t(PyInt_FromLong(2)), PyInt_FromLong(resultNTEYAlias[j].number));
			PyList_Append(theListTeachLeavingEst, tListTeachLeavingEst);
		}

		free(resultNTEYAlias);
		free(listYearAfterA);
		free(listYearA);
		fprintf(file, "\n");
		*/
		printf("Year of 200%d OK!\n", i+1);				
	}

	//fprintf(file, "\n<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n\n");
	//fclose(file);
	return resultNTEY;		

} // end teachLeavingEstYear

int main(){	

	int i;
	for (i = 0; i < 10; i++){
		teachers_Year();
		teachEstYear();
		teachDegYear();
		teachDegEstYear();
		listEstabYear();
		listHolDegYear();
		//teachLeavingEstYear();
	}

	return 0;
}